## ---- Data in ----
## PLZ-5 shape file
plz5 <- st_read("dat/plz-gebiete.shp/plz-gebiete.shp") %>%
  mutate(geometry = st_make_valid(geometry))

## Hedonic rents by PLZ-5 (incldues primary Gemeindeschluessel)
rents <-
  read_xlsx(path = "dat/de_f_und_b/Marktmonitor_20191111.xlsx",
            sheet = "MaMo_PLZ_ID_25") %>%
  dplyr::select(-Objektart, -Objektart_ID, -PreisMiete) %>%
  melt(id.vars = c("PLZ", "AGS05max")) %>%
  rename(year = variable,
         rent = value)

## Rent summary statistics by PLZ-5 (biennial time series)
rss <- lapply(seq(2004, 2018, 2L),
              function (sheet) {
                tmp <-
                  read_xlsx(path = "dat/de_f_und_b/F+B-Marktmietenmonitor_PLZ_2004_2018.xlsx",
                            sheet = as.character(sheet),
                            range = "B1:GR8184")
                
                nms <- tmp %>%
                  slice(3:6) %>%
                  mutate_all(.funs = list(~ifelse(is.na(.), "", .))) %>%
                  as.matrix() %>%
                  (function (x) {
                    for (i in seq_len(nrow(x))) {
                      if (i == 1) {
                        for (j in 2:ncol(x)) {
                          x[i, j] <- ifelse(x[i, j] == "", x[i, j - 1], x[i, j])
                        }
                      } else {
                        for (j in 2:ncol(x)) {
                          x[i, j] <-
                            ifelse(x[i, j] == "" &
                                     x[i - 1, j] == x[i - 1, j - 1], x[i, j - 1], x[i, j])
                        }
                      }
                    }
                    return(apply(x, 2, paste, collapse = " "))
                  })
                names(tmp) <- trimws(nms)
                
                tmp %<>%
                  slice(7:nrow(tmp)) %>%
                  dplyr::select(
                    `Post-leitzahl`,
                    `erstgenannter Gemeinde-schlüssel`,
                    `Gemeindename\r\n(zum erstagenannten Gemeindeschlüssel)`,
                    `Datenbasis (der folgenden Angaben) insgesamt  Anzahl`,
                    `aktuelle Marktmiete insgesamt 5%-Perz.`,
                    `aktuelle Marktmiete insgesamt Median`,
                    `aktuelle Marktmiete insgesamt arith. Mittel`,
                    `aktuelle Marktmiete insgesamt 95%-Perz.`,
                    `aktuelle Marktmiete Referenz-/ Normalwohnung Median`,
                    `aktuelle Marktmiete Referenz-/ Normalwohnung arith. Mittel`,
                    `aktuelle Angebotsstruktur … nach Ausstattungsklassen einfach\r\n(Sonderobj.)`,
                    `aktuelle Angebotsstruktur … nach Ausstattungsklassen normal`,
                    `aktuelle Angebotsstruktur … nach Ausstattungsklassen gehoben`,
                    `aktuelle Angebotsstruktur Ausstattungs-index  arith. Mittel`,
                    `aktuelle Marktperformanz mittlere Angebotsdauer  Tage, Median`
                  ) %>%
                  rename(
                    plz = `Post-leitzahl`,
                    ags5 = `erstgenannter Gemeinde-schlüssel`,
                    ags_nm = `Gemeindename\r\n(zum erstagenannten Gemeindeschlüssel)`,
                    n = `Datenbasis (der folgenden Angaben) insgesamt  Anzahl`,
                    cmr_p05 = `aktuelle Marktmiete insgesamt 5%-Perz.`,
                    cmr_p50 = `aktuelle Marktmiete insgesamt Median`,
                    cmr_arm = `aktuelle Marktmiete insgesamt arith. Mittel`,
                    cmr_p95 = `aktuelle Marktmiete insgesamt 95%-Perz.`,
                    rmr_p50 = `aktuelle Marktmiete Referenz-/ Normalwohnung Median`,
                    rmr_arm = `aktuelle Marktmiete Referenz-/ Normalwohnung arith. Mittel`,
                    fur_low = `aktuelle Angebotsstruktur … nach Ausstattungsklassen einfach\r\n(Sonderobj.)`,
                    fur_med = `aktuelle Angebotsstruktur … nach Ausstattungsklassen normal`,
                    fur_hig = `aktuelle Angebotsstruktur … nach Ausstattungsklassen gehoben`,
                    fur_arm = `aktuelle Angebotsstruktur Ausstattungs-index  arith. Mittel`,
                    prf_day = `aktuelle Marktperformanz mittlere Angebotsdauer  Tage, Median`
                  ) %>%
                  mutate(year = sheet)
                
                return(tmp)
              }) %>%
  bind_rows()

# Fill in missing years
rss_fill <- rss %>%
  dplyr::select(plz, ags5, ags_nm) %>%
  distinct() %>%
  mutate(
    value1 = 2005,
    value2 = 2007,
    value3 = 2009,
    value4 = 2011,
    value5 = 2013,
    value6 = 2015,
    value7 = 2017
  ) %>%
  melt(id.vars = c("plz", "ags5", "ags_nm")) %>%
  dplyr::select(-variable) %>%
  rename(year = value)

# Combine and interpolate
rss %<>%
  bind_rows(rss_fill) %>%
  arrange(plz, year)

rss %<>%
  group_by(plz) %>%
  mutate_at(.vars = vars(-plz,-ags5,-ags_nm),
            .funs = list(~ as.numeric(.)))

rss %<>%
  mutate_at(.vars = vars(-plz,-ags5,-ags_nm, -n),
            .funs = list(~ na.approx(., na.rm = FALSE))) %>%
  ungroup()

# Combine with rents data
rents %<>%
  mutate(year = as.numeric(as.character(year))) %>%
  full_join(rss %>% dplyr::select(-ags5), by = c("PLZ" = "plz", "year")) 

# Combine with PLZ5 geo info
plz5_f_und_b <- plz5 %>%
  dplyr::select(geometry, plz) %>%
  left_join(rents,
            by = c("plz" = "PLZ"))  

# Save interim data objects
save(plz5_f_und_b, file = "dat/proc-data/plz5_f_und_b.RData")
# 